The present invention generally relates to relational databases in computer systems, and more specifically, to using a unique index and/or monotone function to remove group by based on set analysis.
Query language (QL) refers to any computer programming language that requests and retrieves data from database and information systems by sending queries. It works on user entered structured and formal programming command based queries to find and extract data from host databases. Query language may also be termed database query language. SQL is an example of a standard query language for accessing and manipulating databases, and SQL stands for Structured Query Language. SQL can execute queries against a database, retrieve data from a database, insert records in a database, update records in a database, delete records from a database, create new databases, create new tables in a database, etc. In SQL, the GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns. The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book. Indexes can also be unique, like the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there is an index.
The monotone function is a function which has a UNIQUE output value for each UNIQUE input value. Some databases have built in scalar functions and user defined functions (UDF) that are a monotone function. The monotonicity of a function is determined by the function's instinct (or inherent) property itself.